use std::ffi::{c_int, CStr, CString};
use std::rc::Rc;
use chrono::{DateTime, Local};
use sqlite_wasm_rs::{sqlite3, sqlite3_bind_int, sqlite3_bind_int64, sqlite3_bind_null, sqlite3_bind_text, sqlite3_clear_bindings, sqlite3_close, sqlite3_errmsg, sqlite3_exec, sqlite3_finalize, sqlite3_int64, sqlite3_last_insert_rowid, sqlite3_open_v2, sqlite3_prepare_v3, sqlite3_reset, sqlite3_step, sqlite3_stmt, SQLITE_DONE, SQLITE_OK, SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE, SQLITE_STATIC, SQLITE_TRANSIENT};
use crate::db::im::IM_SQL;
use sqlite_wasm_rs::relaxed_idb_vfs::{
    install as install_idb_vfs, RelaxedIdbCfg,
};
use crate::db::row::{get_one, query_count, row_wrap};
use crate::util::time::{format_time, format_time3, format_time_m, format_time_m3};

pub async fn db_create_or_init() -> Db {
    db_init().await;
    let db = Db::open();
    let db_is_created = db.check_db_existed();
    if !db_is_created {
        db_created(db.db());
    }
    db
}

pub struct Db {
    pub(crate) db: Rc<*mut sqlite3>,
}

impl Clone for Db {
    fn clone(&self) -> Self {
        Self {
            db: self.db.clone()
        }
    }
}

impl Drop for Db {
    fn drop(&mut self) {
        let count = Rc::strong_count(&self.db);
        if count < 1 {
            unsafe { sqlite3_close(*self.db); }
        }
    }
}

impl Db {

    pub fn open() -> Db {
        let db = open_db();
        Db { db: Rc::new(db) }
    }

    pub fn db(&self) -> *mut sqlite3 {
        *self.db.clone()
    }

    pub fn exec(&self, label: &'static str, exec_sql: &'static str)->i32 {
        self.exec1(label, exec_sql.to_string())
    }

    pub fn exec1(&self, label: &'static str, exec_sql : String)->i32 {
        let sql = format!("{}\0", exec_sql);
        let c_sql = CStr::from_bytes_with_nul(sql.as_bytes()).unwrap();

        let ret = unsafe {
            let ret = sqlite3_exec(
                self.db(),
                c_sql.as_ptr().cast(),
                None,
                std::ptr::null_mut(),
                std::ptr::null_mut(),
            );

            print_error(self.db(), ret, label);
            ret
        };

        log::info!("db exec1 result {}", ret);
        // assert_eq!(SQLITE_OK, ret);
        ret as i32
    }

    pub fn exec2(&self, label: &'static str, sql: String, params: Vec<DbParamType>) {
        let sql = format!("{}\0", sql);
        let c_sql = CStr::from_bytes_with_nul(sql.as_bytes()).unwrap();

        let mut stmt = std::ptr::null_mut();

        unsafe {
            let ret = sqlite3_prepare_v3(
                self.db(),
                c_sql.as_ptr().cast(),
                -1,
                0,
                &mut stmt as *mut _,
                std::ptr::null_mut(),
            );
            print_error(self.db(), ret, label);

            for (k, v) in params.into_iter().enumerate() {
                v.set_param(stmt, (k+1) as u8);
            }

            let ret2 = sqlite3_step(stmt);
            if ret2 != SQLITE_DONE {
                print_error(self.db(), ret2, label);
            }

            sqlite3_finalize(stmt);
        };
    }

    pub fn batch_exec(&self, label: &'static str, sql: String, params: Vec<Vec<DbParamType>>) {
        let sql = format!("{}\0", sql);
        let c_sql = CStr::from_bytes_with_nul(sql.as_bytes()).unwrap();
        let mut stmt = std::ptr::null_mut();

        let _ret = unsafe {
            let ret = sqlite3_prepare_v3(
                self.db(),
                c_sql.as_ptr().cast(),
                -1,
                0,
                &mut stmt as *mut _,
                std::ptr::null_mut(),
            );
            print_error(self.db(), ret, label);

            for param in params.into_iter() {
                for (k, v) in param.into_iter().enumerate() {
                    v.set_param(stmt, (k + 1) as u8);
                }

                let result = sqlite3_step(stmt);
                log::info!("db exec step result: {:?}", result);
                print_error(self.db(), result, label);

                sqlite3_reset(stmt);           // 保持预处理状态
                sqlite3_clear_bindings(stmt);  // 可选清除绑定
            }

            sqlite3_finalize(stmt);
        };
    }

    pub fn query<T>(&self, label: &'static str, sql: &CStr, params: Vec<DbParamType>, row_wrap2: Box<dyn Fn(*mut sqlite3_stmt) -> T>) -> Vec<T> {
        let mut stmt = std::ptr::null_mut();
        let result = unsafe {
            let ret = unsafe {
                sqlite3_prepare_v3(
                    self.db(),
                    sql.as_ptr().cast(),
                    -1,
                    0,
                    &mut stmt as *mut _,
                    std::ptr::null_mut(),
                )
            };
            print_error(self.db(), ret, label);

            for (k, v) in params.into_iter().enumerate() {
                v.set_param(stmt, (k+1) as u8);
            }

            let result = row_wrap(self.db(), label, stmt, row_wrap2);
            
            sqlite3_clear_bindings(stmt);  // 可选清除绑定
            sqlite3_finalize(stmt);

            result
        };

        result
    }

    pub fn query2<T>(&self, label: &'static str, pre_sql: &str, params: Vec<DbParamType>, row_wrap2: Box<dyn Fn(*mut sqlite3_stmt) -> T>)-> Vec<T> {
        let sql = format!("{}\0", pre_sql);
        let c_sql = CStr::from_bytes_with_nul(sql.as_bytes()).unwrap();
        self.query(label, c_sql, params, row_wrap2)
    }

    pub fn query_one<T>(&self, label: &'static str, pre_sql: String, params: Vec<DbParamType>, row_wrap2: Box<dyn Fn(*mut sqlite3_stmt) -> T>)-> Option<T> {
        let sql = format!("{}\0", pre_sql);
        let c_sql = CStr::from_bytes_with_nul(sql.as_bytes()).unwrap();
        let mut stmt = std::ptr::null_mut();
        let result = unsafe {
            let ret = sqlite3_prepare_v3(
                self.db(),
                c_sql.as_ptr().cast(),
                -1,
                0,
                &mut stmt as *mut _,
                std::ptr::null_mut(),
            );
            print_error(self.db(), ret, label);

            for (k, v) in params.into_iter().enumerate() {
                v.set_param(stmt, (k+1) as u8);
            }

            let result = get_one(stmt, row_wrap2);

            sqlite3_clear_bindings(stmt);  // 可选清除绑定
            sqlite3_finalize(stmt);

            result
        };

        result
    }

    pub fn insert1(&self, label: &'static str, insert_sql: String, params: Vec<DbParamType>) -> u64 {
        let sql = format!("{}\0", insert_sql);
        let c_sql = CStr::from_bytes_with_nul(sql.as_bytes()).unwrap();
        let mut stmt = std::ptr::null_mut();

        let u_id = unsafe {
            sqlite3_prepare_v3(
                self.db(),
                c_sql.as_ptr().cast(),
                -1,
                0,
                &mut stmt as *mut _,
                std::ptr::null_mut(),
            );

            for (k, v) in params.into_iter().enumerate() {
                v.set_param(stmt, (k + 1) as u8);
            }

            let result = sqlite3_step(stmt);
            log::info!("db insert step result: {:?}", result);
            if result != SQLITE_DONE  {
                print_error(self.db(), result, label);
            }

            sqlite3_reset(stmt);           // 保持预处理状态
            sqlite3_clear_bindings(stmt);  // 可选清除绑定
            sqlite3_finalize(stmt);

            let id = sqlite3_last_insert_rowid(self.db());
            let u_id = id.cast_unsigned();
            log::info!("db insert, success: {:?}", u_id);
            u_id
        };
        u_id
    }

    pub fn insert(&self, label: &'static str, insert_sql: &'static str, params: Vec<DbParamType>) -> u64 {
        self.insert1(label, insert_sql.to_string(), params)
    }

    pub fn inserts(&self, label: &'static str, insert_sql: &CStr, params_list: Vec<Vec<DbParamType>>) -> Vec<u64> {
        let mut stmt = std::ptr::null_mut();

        let _ret = unsafe {
            sqlite3_prepare_v3(
                self.db(),
                insert_sql.as_ptr().cast(),
                -1,
                0,
                &mut stmt as *mut _,
                std::ptr::null_mut(),
            )
        };

        let mut results = vec![];
        unsafe {
            for param in params_list.into_iter() {
                for (k, v) in param.into_iter().enumerate() {
                    v.set_param(stmt, (k + 1) as u8);
                }

                let result = sqlite3_step(stmt);
                log::info!("db insert step result: {:?}", result);
                if result != SQLITE_DONE  {
                    print_error(self.db(), result, label);
                }

                let id = sqlite3_last_insert_rowid(self.db());
                let u_id : u64 = id.cast_unsigned();
                log::info!("db insert, success: {:?}", u_id);
                results.push(u_id);

                sqlite3_reset(stmt);           // 保持预处理状态
                sqlite3_clear_bindings(stmt);  // 可选清除绑定
            }

            sqlite3_finalize(stmt);
        };

        results
    }

    pub fn inserts2(&self, label: &'static str, insert_sql: &'static str, params_list: Vec<Vec<DbParamType>>) -> Vec<u64> {
        let sql = format!("{}\0", insert_sql);
        let c_sql = CStr::from_bytes_with_nul(sql.as_bytes()).unwrap();
        self.inserts(label, c_sql, params_list)
    }

    pub fn sum(&self, label: &'static str, sql: &str, params: Vec<DbParamType>) -> usize {
        let exec_sql = format!("{}\0", sql);
        let c_sql = CStr::from_bytes_with_nul(exec_sql.as_bytes()).unwrap();
        self.count(label, c_sql, params)
    }

    pub fn count(&self, label: &'static str, sql: &CStr, params: Vec<DbParamType>) -> usize {
        let mut stmt = std::ptr::null_mut();
        let result = unsafe {
            let ret = sqlite3_prepare_v3(
                self.db(),
                sql.as_ptr().cast(),
                -1,
                0,
                &mut stmt as *mut _,
                std::ptr::null_mut(),
            );

            print_error(self.db(), ret, label);

            for (k, v) in params.into_iter().enumerate() {
                v.set_param(stmt, (k + 1) as u8);
            }

            log::info!("db query count start");
            let result = query_count(stmt);

            sqlite3_finalize(stmt);

            result
        };
        result
    }

    pub fn clean_db(&self, table_name: &'static str) {
        let sql = format!("delete from {}\0", table_name);
        let c_sql = CStr::from_bytes_with_nul(sql.as_bytes()).unwrap();
        let ret = unsafe {
            let ret = sqlite3_exec(
                self.db(),
                c_sql.as_ptr().cast(),
                None,
                std::ptr::null_mut(),
                std::ptr::null_mut(),
            );

            print_error(self.db(), ret, "delete table");
            ret
        };

        log::info!("clean table {} result {}", table_name, ret);
        assert_eq!(SQLITE_OK, ret);
    }

    pub fn check_db_existed(&self) -> bool {
        let sql = c"SELECT 1 FROM sqlite_master WHERE type='table' AND name=?";
        let mut params = Vec::new();
        params.push(DbParamType::Str("im_user"));
        let count = self.count("check table", sql, params);
        if count == 0 {
            return false;
        }

        log::info!("db is existed, result is {:?}", count);
        true
    }
}

fn open_db() -> *mut sqlite3 {
    log::info!("open db");

    let mut db = std::ptr::null_mut();
    let ret = unsafe {
        let ret = sqlite3_open_v2(
            c"im_ylt.db".as_ptr().cast(),
            &mut db as *mut _,
            SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
            std::ptr::null()
        );

        print_error(db, ret, "db open");
        ret
    };

    assert_eq!(SQLITE_OK, ret);
    db
}

async fn db_init() {
    log::info!("db init");
    install_idb_vfs(&RelaxedIdbCfg::default(), true)
        .await
        .unwrap();
    
    

    log::info!("db init end");
}

fn db_created(db: *mut sqlite3) {
    log::info!("db im create start");

    let sql = IM_SQL;

    unsafe {
        let ret = sqlite3_exec(
            db,
            sql.as_ptr().cast(),
            None,
            std::ptr::null_mut(),
            std::ptr::null_mut(),
        );

        print_error(db, ret, "db init");

        log::info!("db im create result {}", ret);
        assert_eq!(SQLITE_OK, ret);
    }
}

#[derive(Debug)]
pub enum DbParamType<'a> {
    Uint(u64),
    Int(i64),
    Byte(i8),
    Bool(bool),
    String(String),
    Str(&'a str),
    DateTime(DateTime<Local>),
    DateTime2(&'a DateTime<Local>),
    DateTime3(&'a Option<DateTime<Local>>),
    DateTimeM(DateTime<Local>),
    DateTimeM2(&'a DateTime<Local>),
    DateTimeM3(&'a Option<DateTime<Local>>),
    Null,
    OptionString(Option<String>),
    OptionStr(Option<&'a String>),
}

impl<'a> DbParamType<'a> {

    pub fn set_param(self, stmt: *mut sqlite3_stmt, column: u8) {
        unsafe  {
            let c_c = column as c_int;
            match self {
                DbParamType::Uint(value) => { sqlite3_bind_int64(stmt, c_c, value as sqlite3_int64);}
                DbParamType::Int(value) => { sqlite3_bind_int(stmt, c_c, value as c_int);}
                DbParamType::Byte(value) => {
                    sqlite3_bind_int(stmt, c_c, value as c_int);
                }
                DbParamType::Bool(value) => { sqlite3_bind_int(stmt, c_c, value as c_int);}
                DbParamType::String(str) => {
                    let param = CString::new(str).unwrap().into_raw();
                    sqlite3_bind_text(stmt, c_c, param, -1, SQLITE_TRANSIENT());
                }
                DbParamType::Str(str) => {
                    let param = CString::new(str).unwrap().into_raw();
                    sqlite3_bind_text(stmt, c_c, param, -1, SQLITE_TRANSIENT());
                }
                DbParamType::DateTime(date_time) => {
                    let time = format_time(&date_time);
                    log::info!("set db param date time: {}", time);
                    let time = CString::new(time).unwrap().into_raw();
                    sqlite3_bind_text(stmt, c_c, time, -1, SQLITE_STATIC());
                }
                DbParamType::DateTime2(date_time) => {
                    let time = format_time(&date_time);
                    let time = CString::new(time).unwrap().into_raw();
                    sqlite3_bind_text(stmt, c_c, time, -1, SQLITE_TRANSIENT());
                }
                DbParamType::DateTime3(date_time) => {
                    let time = format_time3(&date_time);
                    let time = CString::new(time).unwrap().into_raw();
                    sqlite3_bind_text(stmt, c_c, time, -1, SQLITE_TRANSIENT());
                }
                DbParamType::DateTimeM(date_time) => {
                    let time = format_time_m(&date_time);
                    let time = CString::new(time).unwrap().into_raw();
                    sqlite3_bind_text(stmt, c_c, time, -1, SQLITE_STATIC());
                }
                DbParamType::DateTimeM2(date_time) => {
                    let time = format_time_m(&date_time);
                    let time = CString::new(time).unwrap().into_raw();
                    sqlite3_bind_text(stmt, c_c, time, -1, SQLITE_TRANSIENT());
                }
                DbParamType::DateTimeM3(date_time) => {
                    let time = format_time_m3(&date_time);
                    log::info!("set db param date time: {}", time);
                    let time = CString::new(time).unwrap().into_raw();
                    sqlite3_bind_text(stmt, c_c, time, -1, SQLITE_TRANSIENT());
                }
                DbParamType::Null => {
                    sqlite3_bind_null(stmt, c_c);
                }
                DbParamType::OptionString(value) => {
                    match value {
                        None => {sqlite3_bind_null(stmt, c_c);}
                        Some(v) => {
                            let param = CString::new(v).unwrap().into_raw();
                            sqlite3_bind_text(stmt, c_c, param, -1, SQLITE_TRANSIENT());
                        }
                    }
                }
                DbParamType::OptionStr(value) => {
                    match value {
                        None => {sqlite3_bind_null(stmt, c_c);}
                        Some(v) => {
                            let v1 = v.to_string();
                            let param = CString::new(v1).unwrap().into_raw();
                            sqlite3_bind_text(stmt, c_c, param, -1, SQLITE_TRANSIENT());
                        }
                    }
                }
            }
        }
    }
}

pub fn print_error(db: *mut sqlite3, ret: c_int, label: &'static str) {
    unsafe {
        if ret != SQLITE_OK && ret != SQLITE_DONE {
            let error = sqlite3_errmsg(db);
            let cstr = CStr::from_ptr(error).to_str().unwrap();
            log::error!("{} db handle error: {}, {}", label, ret, cstr);
        }
    }
}
